package models;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.persistence.Version;

import com.avaje.ebean.Expr;
import com.avaje.ebean.ExpressionList;

import play.db.ebean.Model;

@Entity
@Table(name="doctor_hospitals")
public class DoctorHospital extends Model{
	
	public static final long serialVersionUID = 1L;

	@Id
	@Column(name="iddoctor_hospitals")
	public long id;
	
	@OneToOne
	@JoinColumn(name="hospital_id")
    public Hospitals hospitals;
    
	@OneToOne
	@JoinColumn(name="iddoctor_details")
    public DoctorDetails doctorDetails;
    
    
    public static Model.Finder<Long,DoctorHospital> find = new Model.Finder(Long.class, DoctorHospital.class);
    public static List<DoctorHospital> getDoctors(String speciality, String doctorName, Integer day,String hospital,String city){
    	List<DoctorHospital> doctors = new ArrayList<DoctorHospital>();
    	ExpressionList<DoctorHospital>	doctorEx=	find
				.join("hospitals")
				.fetch("doctorDetails")
				.fetch("doctorDetails.specialities")
				.fetch("doctorDetails.doctor")
				.where()
				.eq("approved", "Y");

		if(!"".equals(speciality)){
			doctorEx.eq("speciality", speciality);
		}
		StringBuilder s = new StringBuilder();
	   	if(!"".equals(hospital) ){
	   		s.append("hospital_name ='"+hospital+"'");
    	}
    	if(!"".equals(city)){
      		if( s.length() >0){
    			s .append(" and ");
    		}
    		s.append( " hospital_city = '"+city+"'");
    	}
    	if(s.length()>0){
    		List<Hospitals> hospitalList=Hospitals.find.where().add(Expr.raw(s.toString())).findList();
    		List<Long> l= new ArrayList<Long>();
    		for(Hospitals h:hospitalList){
    			l.add(h.id);
    		}
    		doctorEx.in("hospital_id", l);
    	}
		if(!"".equals(doctorName)){
			doctorEx.eq("name", doctorName);
		}
		if(day >0){
			doctorEx
			.filterMany("schedules").eq("day", day);
		}

		doctors= doctorEx.findList();

    	return doctors;
    }
   
    public static List<DoctorHospital> getHospitalsForDoctor(long doctorId) {    
    	List<DoctorHospital> listOfHospitals =find
													.fetch("doctorDetails")
													.fetch("doctorDetails.specialities")
													.fetch("doctorDetails.doctor")
    												.fetch("hospitals")
    												.where()
    												.add(Expr.raw("t2.id="+doctorId))	
    												.findList();
    	return listOfHospitals;
    			
    }

    public static List<DoctorHospital> getHospitalsWithDoctors(Long hospitalId, String speciality,String city) {  
    	StringBuilder s = new StringBuilder();
    	if(null !=hospitalId ){
    		s.append( "hospital_id = "+hospitalId);
    	}
    	if(null !=speciality){
    		if( s.length() >0){
    			s .append(" and ");
    		}
    		s.append( " speciality = '"+speciality+"'");
    	}

    	if(null !=city){
    		if( s.length() >0){
    			s .append(" and ");
    		}
    		s.append( " hospital_city = '"+city+"'");
    	}

    	List<DoctorHospital> listOfHospitals=null;
    	if( s.length() >0){
        	listOfHospitals=find
					.fetch("hospitals")
					.fetch("doctorDetails")
					.fetch("doctorDetails.specialities")
					.fetch("doctorDetails.doctor")
					.where()
					.add(Expr.raw(s.toString()))
					.orderBy("hospital_name,speciality")
					.findList();
    		
    	}else{
        	listOfHospitals=find
					.fetch("hospitals")
					.fetch("doctorDetails")
					.fetch("doctorDetails.specialities")
					.fetch("doctorDetails.doctor")
					.orderBy("hospital_name,speciality")
					.findList();

    	}
    	return listOfHospitals;
    			
    }

    public static List<DoctorHospital> getMatchingDoctorNames(String name, String email){
    	List<DoctorHospital> users = new ArrayList<DoctorHospital>();
    	StringBuilder s = new StringBuilder();
    	if(null != email && email.trim().length()>0){
    		s.append(" email_address ='"+email+"'");
    	}else{
        	s.append(" (soundex(SUBSTRING(name,1, LOCATE(' ', name)) )= soundex(SUBSTRING('")
        	.append(name)
        	.append("' , 1,LOCATE(' ', '")
        	.append(name)
        	.append("') ))) and ( soundex(SUBSTRING(name, LOCATE(' ', name) +1,length(SUBSTRING('")
        	.append(name)
        	.append("', LOCATE(' ', '")
        	.append(name)
        	.append("' ) +1)))) =soundex(SUBSTRING('")
        	.append(name)
        	.append("', LOCATE(' ', '")
        	.append(name)
        	.append("' ) +1))) ");
    	}
    	users=find
				.join("hospitals")
				.fetch("doctorDetails")
				.fetch("doctorDetails.specialities")
				.fetch("doctorDetails.doctor")
				.where()
		.add(Expr.raw(s.toString()))
		.findList();
        	return users;
    }
}
